import calendar
from datetime import datetime
import decimal
import uuid
from flask import Flask, jsonify  
from pylibs import SqlHelper
import os,json
import argparse
import copy
import logging  
logging.basicConfig(level=logging.INFO)  

app = Flask(__name__)  

def initSqlHelper():

    parser = argparse.ArgumentParser(description='ybtool1 参数')  
  
    # 添加一个命令行参数 -c 或 --config，类型为字符串，默认值为 None  
    parser.add_argument('-c', '--config', type=str, help='配置文件信息')  
  
    # 解析命令行参数  
    args = parser.parse_args()  
    
    if args.config is not None:
        configJsonFile = args.config
    else:
        configJsonFile = 'config.json'
    
    if os.path.exists(configJsonFile):  
        # 如果 config_json_file 存在，则加载到 configJson 中  
        with open(configJsonFile, 'r', encoding='utf-8') as file1:  
            configJson = json.load(file1)  

    SqlHelper.dbinfo['host']= configJson['db_host']
    SqlHelper.dbinfo['port']= configJson['db_port']
    SqlHelper.dbinfo['user']= configJson['db_user']
    SqlHelper.dbinfo['passwd']= configJson['db_passwd']
    SqlHelper.dbinfo['db']= configJson['db_database']

# 获取 年度预算 数据  
@app.route('/api1/year-yusuan/<int:year>', methods=['GET'])  
def listYearYusuan(year):   
    sql8="select sum(yusuan_money) as zongyusuan from t_year_yusuan where year_val= %(yearVal)s"
    param8 = {"yearVal": year}
    res8 = SqlHelper.fetch_one(sql8, param8)

    zongyussuan = decimal.Decimal('371209830') 
    if res8 is not None:
       zongyussuan =  res8['zongyusuan']

    sql9="select * from t_year_yusuan where year_val= %(yearVal)s"
    res9 = SqlHelper.fetch_all(sql9, param8)
    if res9 is None:
        res7 = {"code":500,"msg":"未能获得年度预算数据"}
    else:

        for row1 in res9:
            objBili = _calcJindu( row1['yusuan_money'], zongyussuan )
            row1['bili'] = objBili['val2']

        res7 = {"code":0,"msg":"success", "result":res9}
    
    return res7

# 获取 居民医保统筹基金分类支出进度表 数据
@app.route('/api1/get-statist01-data/<int:year>/<int:month>', methods=['GET'])  
def getStatist01Data(year,month):
    sql9 = "select * from t_statist_01 where year_val = %(year_val)s \
          and month_val=%(month_val)s order by sort_no"
    param9 = {"year_val":year ,"month_val": month}
    res9 = SqlHelper.fetch_all(sql9, param9)
    if res9 is None:
        res8 = {"code":500,"msg":"数据还未统计，请先调用筛选数据功能，再加载该数据。"}
    else:
        res8 = {"code":0,"msg":"success", "result":res9}
    
    return res8

# 获取 居民医保统筹基金支出进度表（柱状图+折线图） 数据
@app.route('/api1/get-statist02-data/<int:year>', methods=['GET'])  
def getStatist02Data(year):
    sql9 = "select * from t_statist_02 where year_val = %(year_val)s  order by sort_no"
    param9 = {"year_val":year }
    res9 = SqlHelper.fetch_all(sql9, param9)
    if res9 is None:
        res8 = {"code":500,"msg":"数据还未统计，请先调用筛选数据功能，再加载该数据。"}
    else:
        res8 = {"code":0,"msg":"success", "result":res9}
    
    return res8


# 获取 居民医保统筹基金支出情况表（按区域）（饼图）  数据 
@app.route('/api1/get-statist03-data/<int:year>', methods=['GET'])  
def getStatist03Data(year):
    sql9 = "select * from t_statist_03 where year_val = %(year_val)s  order by sort_no"
    param9 = {"year_val":year }

    res9 = SqlHelper.fetch_all(sql9, param9)
    if res9 is None:
        res8 = {"code":500,"msg":"数据还未统计，请先调用筛选数据功能，再加载该数据。"}
    else:
        res8 = {"code":0,"msg":"success", "result":res9}
    
    return res8


# 获取 居民医保就医地分布（地图） 数据 
@app.route('/api1/get-statist04-data/<int:year>', methods=['GET'])  
def getStatist04Data(year):
    sql9 = "select * from t_statist_04 where year_val = %(year_val)s  order by sort_no"
    param9 = {"year_val":year }

    res9 = SqlHelper.fetch_all(sql9, param9)
    
    if res9 is None:
        res8 = {"code":500,"msg":"数据还未统计，请先调用筛选数据功能，再加载该数据。"}
    else:
        res8 = {"code":0,"msg":"success", "result":res9}
    
    return res8


# 获取 21 
# 运行指标 - 城乡居民门诊支出进度表（堆叠柱状图） 例如  zhuchuType=1  year=2024  month=7
# 运行指标 - 城乡居民住院支出进度表（堆叠柱状图） 例如  zhuchuType=2  year=2024  month=7
@app.route('/api1/get-statist21-data/<int:zhichuType>/<int:year>/<int:month>', methods=['GET'])  
def getStatist05Data(zhichuType,year, month):
    sql9 = "select * from t_statist_21 where year_val = %(year_val)s  and month_val = %(month_val)s \
        and zhichu_type=%(zhichu_type)s order by sort_no"
    param9 = {"year_val":year,'month_val': month, 'zhichu_type': zhichuType }

    res9 = SqlHelper.fetch_all(sql9, param9)
    
    if res9 is None:
        res8 = {"code":500,"msg":"数据还未统计，请先调用筛选数据功能，再加载该数据。"}
    else:
        res8 = {"code":0,"msg":"success", "result":res9}
    
    return res8

# 获取 31 评价指标 - 2024年？月份居民住院综合评价（雷达图) 例如  insuType=390  year=2024  month=7
# 评价指标 - 2024年？月份职工住院综合评价（雷达图) 例如  insuType=310  year=2024  month=7
@app.route('/api1/get-statist31-data/<string:insuType>/<int:year>/<int:month>', methods=['GET'])  
def getStatist31Data(insuType,year, month):
    sql9 = "select * from t_statist_31 where year_val = %(year_val)s  and month_val = %(month_val)s \
        and insu_type=%(insu_type)s order by sort_no"
    param9 = {"year_val":year,'month_val': month, 'insu_type': insuType }

    res9 = SqlHelper.fetch_all(sql9, param9)
    
    if res9 is None:
        res8 = {"code":500,"msg":"数据还未统计，请先调用筛选数据功能，再加载该数据。"}
    else:
        res8 = {"code":0,"msg":"success", "result":res9}
    
    return res8

# 获取 33 评价指标 - 2024年？月份居民住院综合评价（雷达图) 例如  year=2024  month=7
@app.route('/api1/get-statist33-data/<int:year>/<int:month>', methods=['GET'])  
def getStatist33Data(year, month):
    sql9 = "select * from t_statist_33 where year_val = %(year_val)s  and month_val = %(month_val)s \
         order by sort_no"
    param9 = {"year_val":year,'month_val': month  }

    res9 = SqlHelper.fetch_all(sql9, param9)
    
    if res9 is None:
        res8 = {"code":500,"msg":"数据还未统计，请先调用筛选数据功能，再加载该数据。"}
    else:
        res8 = {"code":0,"msg":"success", "result":res9}
    
    return res8


# 获取 34 评价指标 - 2024年？月份居民门诊综合评价（多维条形图) 例如  year=2024  month=7
@app.route('/api1/get-statist34-data/<int:year>/<int:month>', methods=['GET'])  
def getStatist34Data(year, month):
    sql9 = "select * from t_statist_34 where year_val = %(year_val)s  and month_val = %(month_val)s \
         order by sort_no"
    param9 = {"year_val":year,'month_val': month  }

    res9 = SqlHelper.fetch_all(sql9, param9)
    
    if res9 is None:
        res8 = {"code":500,"msg":"数据还未统计，请先调用筛选数据功能，再加载该数据。"}
    else:
        res8 = {"code":0,"msg":"success", "result":res9}
    
    return res8


# 获取 35 评价指标 - 2024年？月份就诊病种（词云) 例如  year=2024  month=7
@app.route('/api1/get-statist35-data/<int:year>/<int:month>', methods=['GET'])  
def getStatist35Data(year, month):
    sql9 = "select * from t_statist_35 where year_val = %(year_val)s  and month_val = %(month_val)s \
         order by sort_no desc"
    param9 = {"year_val":year,'month_val': month  }

    res9 = SqlHelper.fetch_all(sql9, param9)
    
    if res9 is None:
        res8 = {"code":500,"msg":"数据还未统计，请先调用筛选数据功能，再加载该数据。"}
    else:
        res8 = {"code":0,"msg":"success", "result":res9}
    
    return res8


# 获取 41 考核指标 - 2024年？城乡居民基本医疗保险门诊统筹就诊情况 例如  year=2024  month=7
@app.route('/api1/get-statist41-data/<int:year>/<int:month>', methods=['GET'])  
def getStatist41Data(year, month):

    quyear = year-1

    sql9 = "select * from t_statist_41 where year_val = %(year_val)s  and month_val = %(month_val)s \
         order by sort_no"
    
    quparam9 =  {"year_val":quyear,'month_val': month  }
    param9 = {"year_val":year,'month_val': month  }

    qures9 = SqlHelper.fetch_all(sql9, quparam9)
    res9 = SqlHelper.fetch_all(sql9, param9)

    # 合并 两年的 数据
    arr1 = []
    for row1 in res9:
        obj1 = {'fixmedins_code': row1['fixmedins_code'],
                'fixmedins_name': row1['fixmedins_name'],
                'curr_jiuzhen_renci': row1['jiuzhen_renci'],
                'curr_jiuzhen_renshu': row1['jiuzhen_renshu'],
                'curr_renci_rentoubi': row1['renci_rentoubi'],
                'curr_yiliao_zongfeiyong': row1['yiliao_zongfeiyong'],
                'curr_tongchou_jijin_zhifu': row1['tongchou_jijin_zhifu'],
                'curr_cijun_yiliao_feiyong': row1['cijun_yiliao_feiyong'],
                'curr_renjun_yiliao_feiyong': row1['renjun_yiliao_feiyong'],

                'qu_jiuzhen_renci': decimal.Decimal('0.0'),
                'qu_jiuzhen_renshu': decimal.Decimal('0.0'),
                'qu_renci_rentoubi':  decimal.Decimal('0.0'),
                'qu_yiliao_zongfeiyong':  decimal.Decimal('0.0'),
                'qu_tongchou_jijin_zhifu':  decimal.Decimal('0.0'),
                'qu_cijun_yiliao_feiyong':  decimal.Decimal('0.0'),
                'qu_renjun_yiliao_feiyong':  decimal.Decimal('0.0'),
                 }

        # 处理 去年的数据 
        if qures9 is not None:
            for row2 in qures9:
                if row2['fixmedins_code'] == row1['fixmedins_code']:
                    obj1['qu_jiuzhen_renci'] = row2['jiuzhen_renci']
                    obj1['qu_jiuzhen_renshu'] = row2['jiuzhen_renshu']
                    obj1['qu_renci_rentoubi'] = row2['renci_rentoubi']
                    obj1['qu_yiliao_zongfeiyong'] = row2['yiliao_zongfeiyong']
                    obj1['qu_tongchou_jijin_zhifu'] = row2['tongchou_jijin_zhifu']
                    obj1['qu_cijun_yiliao_feiyong'] = row2['cijun_yiliao_feiyong']
                    obj1['qu_renjun_yiliao_feiyong'] = row2['renjun_yiliao_feiyong']

                    break

        # 处理最后的 3个统计字段
        obj1['col_q'] = _calcColx( obj1['curr_yiliao_zongfeiyong'] , obj1['qu_yiliao_zongfeiyong'] )
        obj1['col_r'] = _calcColx( obj1['curr_tongchou_jijin_zhifu'] , obj1['qu_tongchou_jijin_zhifu'] )
        obj1['col_s'] = _calcColx( obj1['curr_renjun_yiliao_feiyong'] , obj1['qu_renjun_yiliao_feiyong'] )

        arr1.append(obj1)
    
    if arr1 is None:
        res8 = {"code":500,"msg":"数据还未统计，请先调用筛选数据功能，再加载该数据。"}
    else:
        res8 = {"code":0,"msg":"success", "result":arr1}
    
    return res8



# 获取 42 考核指标 - 2024年？城乡居民基本医疗保险住院评价指标 例如  year=2024  month=7
@app.route('/api1/get-statist42-data/<int:year>/<int:month>', methods=['GET'])  
def getStatist42Data(year, month):

    quyear = year-1

    sql9 = "select * from t_statist_42 where year_val = %(year_val)s  and month_val = %(month_val)s \
         order by sort_no"
    
    quparam9 =  {"year_val":quyear,'month_val': month  }
    param9 = {"year_val":year,'month_val': month  }

    qures9 = SqlHelper.fetch_all(sql9, quparam9)
    res9 = SqlHelper.fetch_all(sql9, param9)

    # 合并 两年的 数据
    arr1 = []
    for row1 in res9:
        obj1 = {'fixmedins_code': row1['fixmedins_code'],
                'fixmedins_name': row1['fixmedins_name'],

                'curr_yiliao_zongfeiyong': row1['yiliao_zongfeiyong'],
                'curr_jiuzhen_renci': row1['jiuzhen_renci'],
                'curr_jiuzhen_renshu': row1['jiuzhen_renshu'],
                'curr_cijun_feiyong': row1['cijun_feiyong'],
                'curr_renjun_feiyong': row1['renjun_feiyong'],
                'curr_tongchou_jijin_zhifu': row1['tongchou_jijin_zhifu'],
                'curr_shiji_baoxiao_bili': row1['shiji_baoxiao_bili'],
                'curr_zhuyuan_tianshu': row1['zhuyuan_tianshu'],
                'curr_cijun_zhuyuan_tianshu': row1['cijun_zhuyuan_tianshu'],
                'curr_renjun_zhuyuan_tianshu': row1['renjun_zhuyuan_tianshu'],
                'curr_zhuyuan_pingjun_chuangri_feiyong': row1['zhuyuan_pingjun_chuangri_feiyong'],

                'qu_yiliao_zongfeiyong': decimal.Decimal('0.0'),
                'qu_jiuzhen_renci': decimal.Decimal('0.0'),
                'qu_jiuzhen_renshu': decimal.Decimal('0.0'),
                'qu_cijun_feiyong': decimal.Decimal('0.0'),
                'qu_renjun_feiyong': decimal.Decimal('0.0'),
                'qu_tongchou_jijin_zhifu': decimal.Decimal('0.0'),
                'qu_shiji_baoxiao_bili': decimal.Decimal('0.0'),
                'qu_zhuyuan_tianshu': decimal.Decimal('0.0'),
                'qu_cijun_zhuyuan_tianshu': decimal.Decimal('0.0'),
                'qu_renjun_zhuyuan_tianshu': decimal.Decimal('0.0'),
                'qu_zhuyuan_pingjun_chuangri_feiyong': decimal.Decimal('0.0'),

                 }

        # 处理 去年的数据 
        if qures9 is not None:
            for row2 in qures9:
                if row2['fixmedins_code'] == row1['fixmedins_code']:
                    obj1['qu_yiliao_zongfeiyong'] = row2['yiliao_zongfeiyong']
                    obj1['qu_jiuzhen_renci'] = row2['jiuzhen_renci']
                    obj1['qu_jiuzhen_renshu'] = row2['jiuzhen_renshu']
                    obj1['qu_cijun_feiyong'] = row2['cijun_feiyong']
                    obj1['qu_renjun_feiyong'] = row2['renjun_feiyong']
                    obj1['qu_tongchou_jijin_zhifu'] = row2['tongchou_jijin_zhifu']
                    obj1['qu_shiji_baoxiao_bili'] = row2['shiji_baoxiao_bili']
                    obj1['qu_zhuyuan_tianshu'] = row2['zhuyuan_tianshu']
                    obj1['qu_cijun_zhuyuan_tianshu'] = row2['cijun_zhuyuan_tianshu']
                    obj1['qu_renjun_zhuyuan_tianshu'] = row2['renjun_zhuyuan_tianshu']
                    obj1['qu_zhuyuan_pingjun_chuangri_feiyong'] = row2['zhuyuan_pingjun_chuangri_feiyong']

                    break

        # 处理最后的 3个统计字段
        
        obj1['col_y'] = _calcColx( obj1['curr_yiliao_zongfeiyong'] , obj1['qu_yiliao_zongfeiyong'] )
        obj1['col_z'] = _calcColx( obj1['curr_cijun_feiyong'] , obj1['qu_cijun_feiyong']  )
        obj1['col_aa'] = _calcColx( obj1['curr_renjun_feiyong'] , obj1['qu_renjun_feiyong']  )
        obj1['col_ab'] = _calcColx( obj1['curr_shiji_baoxiao_bili'] , obj1['qu_shiji_baoxiao_bili'] )
        obj1['col_ac'] = _calcColx( obj1['curr_cijun_zhuyuan_tianshu'] , obj1['qu_cijun_zhuyuan_tianshu']  )
        obj1['col_ad'] = _calcColx( obj1['curr_renjun_zhuyuan_tianshu'] , obj1['qu_renjun_zhuyuan_tianshu'] )
        obj1['col_ae'] = _calcColx( obj1['curr_zhuyuan_pingjun_chuangri_feiyong'] , obj1['qu_zhuyuan_pingjun_chuangri_feiyong'] )

        arr1.append(obj1)
    
    if arr1 is None:
        res8 = {"code":500,"msg":"数据还未统计，请先调用筛选数据功能，再加载该数据。"}
    else:
        res8 = {"code":0,"msg":"success", "result":arr1}
    
    return res8

# 获取所有定点医院
@app.route('/api1/list-dingdian-yiyuan', methods=['GET'])  
def listDingdianYiyuan():
    # 获取 所有医院清单
    sql9="select * from t_qinghe_ddyy   order by sort_no"
    param9 = {}
    resDdyy = SqlHelper.fetch_all(sql9, param9)
    
    if resDdyy is None:
        res8 = {"code":500,"msg":"未能获取定点医疗机构"}
    else:
        res8 = {"code":0,"msg":"success", "result":resDdyy}
    
    return res8


# 获取 51 医院考核 例如  fixmedinsCode=H13053400080 insuType=390,  medType=21,  year=2024 
# insuType取值  390  城市居民  310  职工
# medType取值  21  住院  11： 门诊   14： 慢性病
### insuType=390 medType=21  医院居民住院支出情况(柱状图）
### insuType=310 medType=21  医院职工住院支出情况(柱状图）
### insuType=310 medType=11  医院职工门诊支出情况(柱状图）
### insuType=390 medType=11  医院居民门诊支出情况(柱状图）
### insuType=310 medType=14  医院职工慢性病支出情况(柱状图）
### insuType=390 medType=14  医院居民慢性病支出情况(柱状图）
@app.route('/api1/get-statist51-data/<string:fixmedinsCode>/<string:insuType>/<string:medType>/<int:year>', methods=['GET'])  
def getStatist51Data(fixmedinsCode,insuType,medType,year):
    sql9 = "select * from t_statist_51 where fixmedins_code = %(fixmedins_code)s and insu_type = %(insu_type)s and med_type = %(med_type)s and \
         year_val = %(year_val)s  \
         order by sort_no"
    param9 = {"fixmedins_code":fixmedinsCode,"insu_type":insuType, "med_type":medType, "year_val":year }

    res9 = SqlHelper.fetch_all(sql9, param9)

    # insutype =='390';medType == '21'  , 获取 分配额度 yusuan_type=2 即住院
    doFenpeiEdu = False
    if insuType== '390' and medType=='21':
        doFenpeiEdu = True
        fenpeiEdu=  _getFenpeiEdu(fixmedinsCode=fixmedinsCode, yusuanType=2,year=year)

    # insutype =='390';medType == '11'  , 获取 分配额度  yusuan_type=1 即门诊
    if insuType== '390' and medType=='11':
        doFenpeiEdu = True
        fenpeiEdu=   _getFenpeiEdu(fixmedinsCode=fixmedinsCode, yusuanType=1,year=year)

    if doFenpeiEdu and res9 is not None:
        zong1 = 0
        zong2 = 0
        for row8  in  res9:
            row8['fenpei_edu'] = fenpeiEdu
            zong1 += fenpeiEdu
            zong2 += row8['tongchou_jijin_zhifu']

            row8['chaochu'] = zong2 - zong1
    
    if res9 is None:
        res8 = {"code":500,"msg":"数据还未统计，请先调用筛选数据功能，再加载该数据。"}
    else:
        res8 = {"code":0,"msg":"success", "result":res9}
    
    return res8

def _getFenpeiEdu( fixmedinsCode, yusuanType, year ):
    sql9 = 'select * from t_qinghe_ddyy_year_yusuan where fixmedins_code = %(fixmedins_code)s \
        and yusuan_type=%(yusuan_type)s and year_val = %(year_val)s limit 1'
    param9 = {"fixmedins_code":fixmedinsCode,"yusuan_type":yusuanType,"year_val":year}
    res9 = SqlHelper.fetch_one(sql9, param9)

    if res9 is None:
        return 0
    else:
        return round( res9['yusuan'] / decimal.Decimal('12') , 4)





@app.route('/api1/test1', methods=['GET'])  
def test1():  

    start1,end1=_getMonthZongYusuan(2024,6)

    return {"code":0, "msg":"success", "result":{"start":start1,"end":end1 } }



#################  各种私有方法

def _calcColx( col1,col2 ):
    if col2 == 0 :
        return 1
    else:
        return round( col1 / col2 - 1 , 4)

def _getYearYusuan(year, yusuanTitle):
    sql9="select * from t_year_yusuan where year_val= %(yearVal)s and yusuan_title = %(yusuanTitle)s"
    param9 = {"yearVal": year,"yusuanTitle":yusuanTitle}
    res9 = SqlHelper.fetch_one(sql9, param9)
    
    if res9 is None:
        return 0
    else:
        return res9['yusuan_money']
    
def _getMonthZongYusuan(year,month):
    sql9="select * from t_month_zongyusuan where year_val= %(yearVal)s and month_val = %(monthVal)s"
    param9 = {"yearVal": year,"monthVal":month}
    res9 = SqlHelper.fetch_one(sql9, param9)
    
    if res9 is None:
        return 0
    else:
        return res9['yusuan_money']
    

### 获取当月开始和结束时间
def _getMonthStartAndEndTime(year,month):
    # 获取月份的第一天  
    start_date = datetime(year, month, 1)  
      
    # 下一月的第一天的 0 点
    nextYear = year
    nextMonth = month+1
    if nextMonth >12:
        nextYear = year+1
        nextMonth = 1

    end_date = datetime(nextYear, nextMonth, 1)  
      
    # 返回开始和结束日期  
    return start_date, end_date  

#### 统计 支出金额
def _statistZhichu(startTime, endTime, subwhere):
    sql9 = f"select sum(hifpPay) as zhichu from t_ybtool1_jiesuan_mingxi where \
        setlTime between %(startSetlTime)s and %(endSetlTime)s {subwhere}"
    param9 = {"startSetlTime": startTime,"endSetlTime":endTime}
    res9 = SqlHelper.fetch_one(sql9, param9)
    
    return res9['zhichu'] if res9['zhichu'] is not None else decimal.Decimal("0.0")


### 计算进度
def _calcJindu(fenzi,fenmu):
    if fenzi is None:
        fenzi = 0.0
    
    if fenmu is None or fenmu ==0 :
        return {"val1":0, "val2":0, "val3": "0%" }
    
    val1 = fenzi/fenmu 
    val2 = round(val1,4) 
    val3 = "%.2f%%" % (val1 * 100) 
    return {"val1":val1, "val2":val2, "val3": val3 }

def _saveStatist01(year,month,zhichuLeixing, yusuan, 
                   dangyueZhichuMoney,wangyueZhichuMoney,yujiZhichuMoney,
                   dangyueZhichuJindu,wangyueZhichuJindu,yujiZhichuJindu,
                   sortNo):
    param9={"id": uuid.uuid4().hex, "yearVal": year, "monthVal": month, "zhichuLeixing": zhichuLeixing,
            "yusuanMoney":yusuan, "dangyueZhichuMoney":dangyueZhichuMoney,"wangyueZhichuMoney":wangyueZhichuMoney,
            "yujiZhichuMoney":yujiZhichuMoney,
            "dangyueZhichuJindu":dangyueZhichuJindu,"wangyueZhichuJindu":wangyueZhichuJindu,
            "yujiZhichuJindu":yujiZhichuJindu, "sortNo":sortNo }

    sql9 = "insert into t_statist_01 (id,year_val,month_val,zhichu_leixing,\
        yusuan_money,dangyue_zhichu_money,wangyue_zhichu_money,yuji_zhichu_money,\
            dangyue_zhichu_jindu,wangyue_zhichu_jindu,yuji_zhichu_jindu,sort_no) \
                values ( %(id)s,%(yearVal)s,%(monthVal)s,%(zhichuLeixing)s,\
                     %(yusuanMoney)s,%(dangyueZhichuMoney)s,%(wangyueZhichuMoney)s,%(yujiZhichuMoney)s,\
                    %(dangyueZhichuJindu)s,%(wangyueZhichuJindu)s,%(yujiZhichuJindu)s,%(sortNo)s ) \
        ON DUPLICATE KEY UPDATE \
          yusuan_money=VALUES(yusuan_money),\
          dangyue_zhichu_money=VALUES(dangyue_zhichu_money),\
          wangyue_zhichu_money=VALUES(wangyue_zhichu_money),\
          yuji_zhichu_money=VALUES(yuji_zhichu_money),\
          dangyue_zhichu_jindu=VALUES(dangyue_zhichu_jindu),\
          wangyue_zhichu_jindu=VALUES(wangyue_zhichu_jindu),\
          yuji_zhichu_jindu=VALUES(yuji_zhichu_jindu),\
          sort_no=VALUES(sort_no)\
            "
    SqlHelper.insert(sql9, param9)

def _saveStatist02(year, month, yusuanMoney, dangyueZhichuMoney, monthZhichuWanchenglv, monthLeijiZhichulv, sortNo):

    sql9 = "insert into t_statist_02 (id,year_val,month_val,yusuan_money,\
        dangyue_zhichu_money,dangyue_zhichu_wanchenglv,dangyue_leiji_zhichulv,sort_no) \
                values ( %(id)s,%(yearVal)s,%(monthVal)s,%(yusuanMoney)s,\
                    %(dangyueZhichuMoney)s,%(monthZhichuWanchenglv)s,%(monthLeijiZhichulv)s,%(sortNo)s ) \
        ON DUPLICATE KEY UPDATE \
          yusuan_money=VALUES(yusuan_money),\
          dangyue_zhichu_money=VALUES(dangyue_zhichu_money),\
           dangyue_zhichu_wanchenglv=VALUES(dangyue_zhichu_wanchenglv),\
            dangyue_leiji_zhichulv=VALUES(dangyue_leiji_zhichulv),\
          sort_no=VALUES(sort_no) \
            "
    param9={"id": uuid.uuid4().hex, "yearVal": year, "monthVal": month, 
           "yusuanMoney":yusuanMoney, "dangyueZhichuMoney":dangyueZhichuMoney,
           "monthZhichuWanchenglv":monthZhichuWanchenglv,"monthLeijiZhichulv":monthLeijiZhichulv,
           "sortNo": sortNo
            }
    
    SqlHelper.insert(sql9, param9)


def _saveStatist03(year,zhichuQuyu,qunianLeijiMoney,dangnianLeijiMoney,
                   qunianLeijiBili,dangnianLeijiBili,sortNo):
    

    sql9 = "insert into t_statist_03 (id,year_val,zhichu_quyu,\
        qunian_leiji_money,dangnian_leiji_money,\
            qunian_leiji_bili,dangnian_leiji_bili,sort_no) \
                values ( %(id)s,%(year_val)s,%(zhichu_quyu)s,\
        %(qunian_leiji_money)s,%(dangnian_leiji_money)s,\
        %(qunian_leiji_bili)s,%(dangnian_leiji_bili)s ,%(sort_no)s ) \
        ON DUPLICATE KEY UPDATE \
          qunian_leiji_money=VALUES(qunian_leiji_money),\
          dangnian_leiji_money=VALUES(dangnian_leiji_money),\
          qunian_leiji_bili=VALUES(qunian_leiji_bili),\
          dangnian_leiji_bili=VALUES(dangnian_leiji_bili),\
          sort_no=VALUES(sort_no) \
            "
    param9={"id": uuid.uuid4().hex, "year_val": year, "zhichu_quyu": zhichuQuyu, 
           "qunian_leiji_money":qunianLeijiMoney, "dangnian_leiji_money":dangnianLeijiMoney,
           "qunian_leiji_bili":qunianLeijiBili, "dangnian_leiji_bili":dangnianLeijiBili,
           "sort_no": sortNo
            }
    
    SqlHelper.insert(sql9, param9)

if __name__ == '__main__':  
    initSqlHelper()

    app.run(debug=True, port=8003, host='0.0.0.0')

